import pymysql

class DBtools:
    @classmethod
    def __get_mysql(cls):
        conn=pymysql.connect(host='47.108.206.100',port=3306,user='student',password='stu2022',charset='utf8',database='mall')
        return conn

    @classmethod#查询一条
    def select_one(cls,sql):
        cursor=None
        conn=None
        res=None
        try:
            conn=cls.__get_mysql()# 1.建立连接
            cursor=conn.cursor(pymysql.cursors.DictCursor)#2.获取游标
            cursor.execute(sql)
            res=cursor.fetchone()
        except Exception as e:
            print('查询一条时遇到错误'+str(e))
        finally:
            cursor.close()
            conn.close()
            return res


    @classmethod  # 查询多条
    def select_many(cls, sql,num):
        cursor = None
        conn = None
        res = None
        try:
            conn = cls.__get_mysql()  # 1.建立连接
            cursor = conn.cursor(pymysql.cursors.DictCursor)  # 2.获取游标
            cursor.execute(sql)
            res = cursor.fetchmany(num)
        except Exception as e:
            print('查询一条时遇到错误' + str(e))
        finally:
            cursor.close()
            conn.close()
            return res


    @classmethod  # 查询一条
    def select_all(cls, sql):
        cursor = None
        conn = None
        res = None
        try:
            conn = cls.__get_mysql()  # 1.建立连接
            cursor = conn.cursor(pymysql.cursors.DictCursor)  # 2.获取游标
            cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as e:
            print('查询一条时遇到错误' + str(e))
        finally:
            cursor.close()
            conn.close()
            return res


    #增删改
    @classmethod
    def iud_db(cls,sql):
        cursor = None
        conn = None
        try:
            conn=cls.__get_mysql()
            cursor=conn.cursor()
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print('进行增删改操作出错'+str(e))
            conn.rollback()
        finally:
            cursor.close()
            conn.close()






if __name__ == '__main__':
    res=DBtools.select_one('select * from student')
    res1=DBtools.select_many('select * from student',3)
    # res2=DBtools.select_all('select * from student')
    print(res)
    print(res1)
    # print(res2)

    # sql1 = 'update student set money =17000 where stu_name="曹操";'
    # DBtools.iud_db(sql1)
    # sql2 = 'insert into student values(10,"itsrc-016","葫芦娃",18,11,45000)'
    # DBtools.iud_db(sql2)
    # sql3 = 'delete from student where stu_name="袁术";'
    # DBtools.iud_db(sql3)








